﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace XYAuto.ChiTu.Excel
{
    public static class NpoiExcelHelper
    {
        /// <summary>
        /// 从Excel文件中读取Excel到Table
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="isCompatible"></param>
        /// <returns></returns>
        public static DataTable ReadExcelToDataTable(string fileName, bool isCompatible)
        {
            using (var stream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                return ReadExcelToDataTable(stream, isCompatible);
            }
        }

        public static DataTable ReadExcelToDataTable(string fileName, bool isCompatible, string sheetName)
        {
            using (var stream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                return ReadExcelToDataTable(stream, isCompatible, sheetName);
            }
        }

        /// <summary>
        /// 从Stream流中读取Excel到Table
        /// </summary>
        /// <param name="fileStream"></param>
        /// <param name="isCompatible"></param>
        /// <returns></returns>
        public static DataTable ReadExcelToDataTable(Stream fileStream, bool isCompatible)
        {
            fileStream.Position = 0;
            var workBook = GetWorkBook(fileStream, isCompatible);
            var sheet = GetSheetFromWorkBook(workBook);

            if (Equals(sheet, null))
            {
                return null;
            }

            var dt = new DataTable();
            CreateDataColumn(sheet, dt);
            CreateDataTable(sheet, dt);
            return dt;
        }

        public static DataTable ReadExcelToDataTable(Stream fileStream, bool isCompatible, string sheetName)
        {
            fileStream.Position = 0;
            var workBook = GetWorkBook(fileStream, isCompatible);
            var sheet = GetSheetFromWorkBook(workBook, sheetName);

            if (Equals(sheet, null))
            {
                return null;
            }

            var dt = new DataTable();
            CreateDataColumn(sheet, dt);
            CreateDataTable(sheet, dt);
            return dt;
        }

        public static IEnumerable<T> ReadExcelToList<T>(ISheet sheet, Dictionary<string, PropertyInfo> propertyInfos)
            where T : class, new()
        {
            IList<T> list = new List<T>();

            //遍历每一行数据
            for (int i = sheet.FirstRowNum + 1, len = sheet.LastRowNum + 1; i < len; i++)
            {
                T t = new T();
                IRow row = sheet.GetRow(i);
                var cellIndex = 0;
                foreach (var info in propertyInfos)
                {
                    ICell cell = row.GetCell(cellIndex);
                    object cellValue = ValueType(cell);
                    typeof(T).GetProperty(info.Key).SetValue(t, cellValue, null);
                    cellIndex++;
                }

                list.Add(t);
            }
            return list;
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="isCompatible"></param>
        /// <param name="sheetName"></param>
        /// <param name="propertyInfos"></param>
        /// <param name="headMsg"></param>
        /// <returns></returns>
        public static MemoryStream ExportExcelToMeory<T>(IEnumerable<T> list, bool isCompatible, string sheetName,
            Dictionary<string, PropertyInfo> propertyInfos, string headMsg)
            where T : class
        {
            var workbook =
                string.IsNullOrWhiteSpace(headMsg) ?
                GetWorkBookFromMemoryStream<T>(list, isCompatible, sheetName, propertyInfos) :
                 GetWorkBookFromMemoryStream<T>(list, isCompatible, sheetName, propertyInfos, headMsg);
            var ms = new MemoryStream();
            workbook.Write(ms);
            return ms;
        }

        /// <summary>
        /// 将数据集合导入Workbook中
        /// </summary>
        /// <typeparam name="T">Excel实体类型</typeparam>
        /// <param name="list">数据集合</param>
        /// <param name="isCompatible">是否兼容模式</param>
        /// <param name="sheetName">要生成的Excel文档Sheet页名称</param>
        /// <param name="propertyInfos"></param>
        /// <returns></returns>
        private static IWorkbook GetWorkBookFromMemoryStream<T>(IEnumerable<T> list, bool isCompatible, string sheetName,
            Dictionary<string, PropertyInfo> propertyInfos)
        {
            var workbook = CreateWorkbook(isCompatible);
            var sheet = workbook.CreateSheet(sheetName);
            var headerRow = sheet.CreateRow(0);

            //插入标题行
            var headerCellIndex = 0;

            foreach (var info in propertyInfos)
            {
                headerRow.CreateCell(headerCellIndex).SetCellValue(info.Key);
                headerCellIndex++;
            }

            //插入内容行
            var t = typeof(T);
            var rowIndex = 1;
            foreach (T item in list)
            {
                var dataRow = sheet.CreateRow(rowIndex);

                var cellIndex = 0;
                foreach (var info in propertyInfos)
                {
                    var pValue = t.GetProperty(info.Value.Name).GetValue(item, null);
                    dataRow.CreateCell(cellIndex).SetCellValue((pValue ?? "").ToString());
                    cellIndex++;
                }
                rowIndex++;
            }
            return workbook;
        }

        private static IWorkbook GetWorkBookFromMemoryStream<T>(IEnumerable<T> list, bool isCompatible, string sheetName,
            Dictionary<string, PropertyInfo> propertyInfos, string headMsg)
        {
            var workbook = CreateWorkbook(isCompatible);
            var sheet = workbook.CreateSheet(sheetName);

            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, propertyInfos.Count - 1));

            IRow row0 = sheet.CreateRow(0);
            row0.Height = 50 * 50;
            ICell icell1Top0 = row0.CreateCell(0);
            icell1Top0.CellStyle = NpoiCellStyle.Getcellstyle(workbook, NpoiCellStyle.stylexls.头);
            icell1Top0.SetCellValue(headMsg);

            //插入标题行
            var headerCellIndex = 0;
            var startTitle = 0;
            if (!string.IsNullOrWhiteSpace(headMsg))
            {
                startTitle++;
            }
            var headerRow = sheet.CreateRow(startTitle);
            foreach (var info in propertyInfos)
            {
                headerRow.CreateCell(headerCellIndex).SetCellValue(info.Key);
                headerCellIndex++;
            }

            //插入内容行
            var t = typeof(T);
            var rowIndex = 1;
            if (!string.IsNullOrWhiteSpace(headMsg))
            {
                rowIndex++;
            }
            foreach (T item in list)
            {
                var dataRow = sheet.CreateRow(rowIndex);

                var cellIndex = 0;
                foreach (var info in propertyInfos)
                {
                    var pValue = t.GetProperty(info.Value.Name).GetValue(item, null);
                    dataRow.CreateCell(cellIndex).SetCellValue((pValue ?? "").ToString());
                    cellIndex++;
                }
                rowIndex++;
            }
            return workbook;
        }

        /// <summary>
        /// 转换Excel数据格式
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object ValueType(ICell cell)
        {
            object cellValue = null;
            switch (cell.CellType)
            {
                case CellType.String: //文本
                    cellValue = cell.StringCellValue;
                    break;

                case CellType.Numeric: //数值
                    cellValue = Convert.ToInt32(cell.NumericCellValue);
                    break;

                case CellType.Boolean: //bool
                    cellValue = cell.BooleanCellValue;
                    break;

                case CellType.Blank: //空白
                    cellValue = "";
                    break;

                default:
                    cellValue = "ERROR";
                    break;
            }
            return cellValue;
        }

        /// <summary>
        /// 从Workbook对象中获取Sheet对象
        /// </summary>
        /// <param name="workBook"></param>
        /// <returns></returns>
        private static ISheet GetSheetFromWorkBook(IWorkbook workBook)
        {
            var sheet = workBook.GetSheetAt(workBook.ActiveSheetIndex);
            return sheet;
        }

        public static ISheet GetSheetFromWorkBook(IWorkbook workBook, string sheetName)
        {
            var sheet = workBook.GetSheet(sheetName);
            if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
            {
                sheet = workBook.GetSheetAt(0);
            }
            return sheet;
        }

        /// <summary>
        /// 从数据流转换为Workbook对象
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="isCompatible"></param>
        /// <returns></returns>
        private static IWorkbook GetWorkBook(Stream stream, bool isCompatible)
        {
            var workbook = default(IWorkbook);
            if (!isCompatible)
                workbook = new XSSFWorkbook(stream);
            else
                workbook = new HSSFWorkbook(stream);
            return workbook;
        }

        private static void CreateDataTable(ISheet sheet, DataTable dt)
        {
            for (var i = 1; i <= sheet.LastRowNum; i++)
            {
                var sheetRow = sheet.GetRow(i);
                if (sheetRow == null) continue;
                var dataRow = dt.NewRow();
                for (var j = 0; j < sheetRow.LastCellNum; j++)
                {
                    ICell cell = sheetRow.GetCell(j);

                    if (cell == null || string.IsNullOrWhiteSpace(cell.ToString()))
                    {
                        dataRow[j] = "";
                        continue;
                    }
                    dataRow[j] = cell.ToString();
                }

                dt.Rows.Add(dataRow);
            }
        }

        /// <summary>
        /// 创建
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="dt"></param>
        private static void CreateDataColumn(ISheet sheet, DataTable dt)
        {
            var firstRow = sheet.GetRow(0);
            for (var i = 0; i < firstRow.LastCellNum; i++)
            {
                var column = new DataColumn(firstRow.GetCell(i).StringCellValue);
                dt.Columns.Add(column);
            }
        }

        /// <summary>
        /// 创建工作薄
        /// </summary>
        /// <param name="isCompatible"></param>
        /// <returns></returns>
        private static IWorkbook CreateWorkbook(bool isCompatible)
        {
            if (isCompatible)
            {
                return new HSSFWorkbook();
            }
            return new XSSFWorkbook();
        }

        /// <summary>
        /// 创建表格头单元格
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        private static ICellStyle GetCellStyle(IWorkbook workbook)
        {
            var style = workbook.CreateCellStyle();
            style.FillPattern = FillPattern.SolidForeground;
            style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;

            return style;
        }
    }
}